from odoo import api, fields, models, _
import io
import base64
from datetime import datetime, date
from odoo.exceptions import UserError
try:
    import xlsxwriter
except Exception:  # pragma: no cover
    xlsxwriter = None


class DocumentsPackingListWizard(models.TransientModel):
    _name = "documents.packing_list.wizard"
    _description = "装箱清单向导"

    language_mode = fields.Selection(
        selection=[("zh", "中文"), ("en", "英文"), ("it", "意大利文")],
        string="语言",
        default="zh",
        required=True,
    )
    export_format = fields.Selection(
        selection=[("xlsx", "XLSX")],
        string="导出格式",
        default="xlsx",
        required=True,
    )
    date = fields.Date(string="日期", default=fields.Date.context_today, required=True)
    company_id = fields.Many2one(
        comodel_name="res.company",
        string="公司",
        default=lambda self: self.env.company,
        required=True,
    )
    partner_id = fields.Many2one("res.partner", string="客户")

    package_ids = fields.Many2many(
        comodel_name="stock.quant.package",
        relation="documents_packing_list_wizard_package_rel",
        column1="wizard_id",
        column2="package_id",
        string="包装",
        required=True,
    )
    note = fields.Text(string="备注")

    line_ids = fields.One2many(
        comodel_name="documents.packing_list.wizard.line",
        inverse_name="wizard_id",
        string="明细行",
    )
    group_ids = fields.One2many(
        comodel_name="documents.packing_list.wizard.group",
        inverse_name="wizard_id",
        string="分组",
    )
    total_volume = fields.Float(string="总体积")

    # Generated file for download
    file_name = fields.Char(string="文件名", readonly=True)
    file_data = fields.Binary(string="文件", readonly=True)

    @api.onchange("package_ids")
    def _onchange_package_ids(self):
        """Auto-set the customer based on selected packages and build lines/groups.

        Tries to infer the customer by tracing package -> move lines -> picking/customer
        or sale order on the related move's sale line.
        """
        self.ensure_one()
        if not self.package_ids:
            self.partner_id = False
            self.line_ids = [(5, 0, 0)]
            self.group_ids = [(5, 0, 0)]
            self.total_volume = 0.0
            return

        StockMoveLine = self.env["stock.move.line"]
        move_lines = StockMoveLine.search([
            "|",
            ("result_package_id", "in", self.package_ids.ids),
            ("package_id", "in", self.package_ids.ids),
        ])

        # Infer a single partner for header convenience
        inferred_partner_ids = set()
        package_to_partner = {}
        for move_line in move_lines:
            picking_partner = move_line.picking_id.partner_id
            candidate_partner = picking_partner or move_line.move_id.sale_line_id.order_id.partner_id
            if candidate_partner:
                inferred_partner_ids.add(candidate_partner.id)
                # Map package to partner
                pkg = move_line.result_package_id or move_line.package_id
                if pkg:
                    package_to_partner[pkg.id] = candidate_partner.id
        if len(inferred_partner_ids) == 1:
            self.partner_id = list(inferred_partner_ids)[0]

        # Build detail lines per package
        new_lines = []
        total_volume = 0.0
        for package in self.package_ids:
            partner_id = package_to_partner.get(package.id) or package.owner_id.id or False
            ptype = package.package_type_id
            length = ptype.packaging_length if ptype else 0.0
            width = ptype.width if ptype else 0.0
            height = ptype.height if ptype else 0.0
            volume = (length or 0.0) * (width or 0.0) * (height or 0.0)
            total_volume += volume
            new_lines.append((0, 0, {
                "partner_id": partner_id,
                "package_id": package.id,
                "package_type_id": ptype.id if ptype else False,
                "length": length,
                "width": width,
                "height": height,
                "volume": volume,
            }))

        # Assign group numbers by distinct partner ordering by name/id
        # Create groups with package counts and group volume
        groups_by_partner = {}
        for line_vals in new_lines:
            vals = line_vals[2]
            partner_id = vals.get("partner_id") or 0
            groups_by_partner.setdefault(partner_id, {"package_count": 0, "volume": 0.0})
            groups_by_partner[partner_id]["package_count"] += 1
            groups_by_partner[partner_id]["volume"] += vals.get("volume") or 0.0

        # Sort partners by display name for consistent numbering
        ordered_partner_ids = [pid for pid in groups_by_partner.keys()]
        if ordered_partner_ids:
            partners = self.env["res.partner"].browse([pid for pid in ordered_partner_ids if pid])
            partners_by_id = {p.id: p for p in partners}
            ordered_partner_ids.sort(key=lambda pid: (partners_by_id.get(pid).display_name if pid in partners_by_id else "~"))
        number_by_partner = {pid: idx + 1 for idx, pid in enumerate(ordered_partner_ids)}

        # Write number into lines
        numbered_lines = []
        for line_vals in new_lines:
            vals = dict(line_vals[2])
            partner_id = vals.get("partner_id") or 0
            vals["number"] = number_by_partner.get(partner_id, 0)
            numbered_lines.append((0, 0, vals))

        # Build group records
        new_groups = []
        for partner_id, agg in groups_by_partner.items():
            new_groups.append((0, 0, {
                "partner_id": partner_id or False,
                "number": number_by_partner.get(partner_id, 0),
                "package_count": agg["package_count"],
                "volume": agg["volume"],
            }))

        self.line_ids = [(5, 0, 0)] + numbered_lines
        self.group_ids = [(5, 0, 0)] + new_groups
        self.total_volume = total_volume

    def _get_labels(self):
        """Return localized column labels based on selected language."""
        lang = self.language_mode or "zh"
        if lang == "en":
            return {
                "package": "Package",
                "pack_date": "Pack Date",
                "last_updated": "Last Updated on",
                "product": "Product",
                "description": "Description",
                "origin": "Origin",
                "client_ref": "Customer reference",
                "qty_done": "Quantity",
                "price_unit": "Unit Price",
                "amount": "Amount",
                "package_type": "Package Type",
                "length": "Length (cm)",
                "width": "Width (cm)",
                "height": "Height (cm)",
                "total_qty": "Total Qty",
                "total_amount": "Total Amount",
            }
        if lang == "it":
            return {
                "package": "Pacco",
                "pack_date": "Data di imballaggio",
                "last_updated": "Ultimo aggiornamento",
                "product": "Prodotto",
                "description": "Descrizione",
                "origin": "Origine",
                "client_ref": "Riferimento del cliente",
                "qty_done": "Quantità",
                "price_unit": "Prezzo unitario",
                "amount": "Importo",
                "package_type": "Tipo di collo",
                "length": "Lunghezza (cm)",
                "width": "Larghezza (cm)",
                "height": "Altezza (cm)",
                "total_qty": "Quantità totale",
                "total_amount": "Importo totale",
            }
        return {
            "package": "包裹名称",
            "pack_date": "拣货日期",
            "last_updated": "拣货明细更新日期",
            "product": "产品",
            "description": "描述",
            "origin": "原产地",
            "client_ref": "客户参考号",
            "qty_done": "数量",
            "price_unit": "单价",
            "amount": "金额",
            "package_type": "包裹类型",
            "length": "长度(cm)",
            "width": "宽度(cm)",
            "height": "高度(cm)",
            "total_qty": "总数量",
            "total_amount": "总金额",
        }

    def _get_pack_date(self, package):
        """Best-effort pack date for a package."""
        pack_date = getattr(package, "pack_date", False)
        if pack_date:
            return fields.Date.to_date(pack_date)
        # fallback: latest done move line date in this package, else create date
        StockMoveLine = self.env["stock.move.line"]
        ml = StockMoveLine.search([
            "|",
            ("result_package_id", "=", package.id),
            ("package_id", "=", package.id),
        ], order="date desc", limit=1)
        if ml and ml.date:
            return fields.Date.to_date(ml.date)
        return fields.Date.to_date(package.create_date)

    def action_export(self):
        self.ensure_one()
        if self.export_format != "xlsx":
            return {"type": "ir.actions.act_window_close"}
        if not xlsxwriter:
            raise UserError(_("The Python package 'xlsxwriter' is required to export XLSX files."))
        if not self.package_ids:
            raise UserError(_("Please select at least one package."))

        labels = self._get_labels()
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})
        worksheet = workbook.add_worksheet("Packing List")
        # 打印设置：A4 纵向，按页宽缩放，水平居中，边距
        try:
            worksheet.set_portrait()
            worksheet.set_paper(9)  # A4
            worksheet.fit_to_pages(1, 0)
            worksheet.center_horizontally()
            worksheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
        except Exception:
            pass

        # 定义格式
        fmt_text = workbook.add_format({
            'align': 'left',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
        })
        fmt_date = workbook.add_format({
            'align': 'left',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
            'num_format': 'yyyy-mm-dd',
        })
        fmt_datetime = workbook.add_format({
            'align': 'left',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
            'num_format': 'yyyy-mm-dd hh:mm:ss',
        })
        fmt_qty = workbook.add_format({
            'align': 'right',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
            'num_format': '#,##0.00',
        })
        # 依据系统货币符号与位置设置价格/金额格式
        company_currency = self.env.company.currency_id
        _currency_symbol = (company_currency.symbol or '')
        _currency_position = (company_currency.position or 'before')
        _currency_symbol_escaped = _currency_symbol.replace('"', '""')
        _price_num_format = f'"{_currency_symbol_escaped}"#,##0.00' if _currency_position == 'before' else f'#,##0.00"{_currency_symbol_escaped}"'
        fmt_price = workbook.add_format({
            'align': 'right',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
            'num_format': _price_num_format,
        })
        fmt_amount = workbook.add_format({
            'align': 'right',
            'valign': 'top',
            'text_wrap': True,
            'font_size': 10,
            'num_format': _price_num_format,
        })

        # Column widths
        widths = [16, 18, 18, 40, 14, 22, 10, 14, 16, 18, 12, 12, 12]
        for idx, w in enumerate(widths):
            worksheet.set_column(idx, idx, w)

        # Headers
        headers = [
            labels["package"], labels["last_updated"], labels["product"],
            labels["description"], labels["origin"], labels["client_ref"],
            labels["qty_done"], labels["price_unit"], labels["amount"],
            labels["package_type"], labels["length"], labels["width"], labels["height"],
        ]
        for col, title in enumerate(headers):
            worksheet.write(0, col, title, fmt_text)

        # Data rows
        row = 1
        total_qty = 0.0
        total_amount = 0.0

        packages = self.package_ids.sorted(key=lambda p: p.name or "")
        for package in packages:
            pack_date = self._get_pack_date(package)
            ptype = package.package_type_id
            length = getattr(ptype, "packaging_length", 0.0) or 0.0
            width = getattr(ptype, "width", 0.0) or 0.0
            height = getattr(ptype, "height", 0.0) or 0.0

            # 改为使用“汇总预览明细”作为导出数据源
            lines = package.preview_merged_quant_line_ids.sorted(
                key=lambda l: (
                    l.product_id.default_code or l.product_id.display_name or "",
                    l.customer_reference or "",
                )
            )

            if not lines:
                worksheet.write(row, 0, package.name or "", fmt_text)
                # 无产品行时，不再显示包裹级日期；该列为“拣货明细更新日期”
                worksheet.write(row, 1, "", fmt_text)
                worksheet.write(row, 9, ptype.display_name if ptype else "", fmt_text)
                worksheet.write_number(row, 10, length, fmt_qty)
                worksheet.write_number(row, 11, width, fmt_qty)
                worksheet.write_number(row, 12, height, fmt_qty)
                row += 1
                continue

            for idx, line in enumerate(lines):
                product = line.product_id
                tmpl = product.product_tmpl_id
                # 产品编码：default_code 优先；兼容条形码/new_variable
                product_code = product.default_code or tmpl.barcode or getattr(tmpl, "new_variable", None) or ""
                description = product.name or ""

                # origin: 以汇总行的 origin_name 为准，空则回退产品模板
                origin_name = (line.origin_name or getattr(tmpl, "origin", None)) or ""
                client_ref = line.customer_reference or ""
                qty = line.quantity or 0.0
                price_unit = line.unit_price or (line.sale_price / qty if qty else 0.0)
                amount = line.sale_price or line.total_price or (qty * price_unit)

                # print package info once per package
                if idx == 0:
                    worksheet.write(row, 0, package.name or "", fmt_text)
                    worksheet.write(row, 9, ptype.display_name if ptype else "", fmt_text)
                    worksheet.write_number(row, 10, length, fmt_qty)
                    worksheet.write_number(row, 11, width, fmt_qty)
                    worksheet.write_number(row, 12, height, fmt_qty)
                else:
                    worksheet.write(row, 0, "", fmt_text)
                    worksheet.write(row, 9, "", fmt_text)
                    worksheet.write(row, 10, "", fmt_text)
                    worksheet.write(row, 11, "", fmt_text)
                    worksheet.write(row, 12, "", fmt_text)

                # 每个产品行的“拣货明细更新日期”（行的 write_date），带时间
                write_dt = getattr(line, 'write_date', False)
                if write_dt:
                    try:
                        local_dt = fields.Datetime.context_timestamp(self, write_dt)
                        # xlsxwriter 期望 naive datetime
                        worksheet.write_datetime(row, 1, local_dt.replace(tzinfo=None), fmt_datetime)
                    except Exception:
                        worksheet.write(row, 1, str(write_dt), fmt_text)
                else:
                    worksheet.write(row, 1, "", fmt_text)

                worksheet.write(row, 2, product_code, fmt_text)
                worksheet.write(row, 3, description, fmt_text)
                worksheet.write(row, 4, origin_name, fmt_text)
                worksheet.write(row, 5, client_ref, fmt_text)
                worksheet.write_number(row, 6, qty, fmt_qty)
                worksheet.write_number(row, 7, price_unit, fmt_price)
                worksheet.write_number(row, 8, amount, fmt_amount)

                total_qty += qty
                total_amount += amount
                row += 1
            
            # Add empty row after each package
            row += 1

        # Totals row
        worksheet.write(row, 6, f"{labels['total_qty']}:", fmt_text)
        worksheet.write_number(row, 7, total_qty, fmt_qty)
        worksheet.write(row, 8, f"{labels['total_amount']}:", fmt_text)
        worksheet.write_number(row, 9, total_amount, fmt_amount)

        workbook.close()
        output.seek(0)
        data = output.read()

        filename = f"packing_list_{fields.Date.context_today(self)}.xlsx"
        self.write({
            "file_name": filename,
            "file_data": base64.b64encode(data),
        })

        return {
            "type": "ir.actions.act_url",
            "url": (
                f"/web/content/?model={self._name}&id={self.id}"
                f"&field=file_data&filename_field=file_name&download=true"
            ),
            "target": "self",
        }


class DocumentsPackingListWizardLine(models.TransientModel):
    _name = "documents.packing_list.wizard.line"
    _description = "Packing List Wizard Line"

    wizard_id = fields.Many2one(
        comodel_name="documents.packing_list.wizard",
        required=True,
        ondelete="cascade",
    )
    number = fields.Integer(string="Number")
    partner_id = fields.Many2one("res.partner", string="Customer")
    package_id = fields.Many2one("stock.quant.package", string="Package")
    package_type_id = fields.Many2one("stock.package.type", string="Package Type")
    length = fields.Float(string="Length")
    width = fields.Float(string="Width")
    height = fields.Float(string="Height")
    volume = fields.Float(string="Volume")


class DocumentsPackingListWizardGroup(models.TransientModel):
    _name = "documents.packing_list.wizard.group"
    _description = "Packing List Wizard Group"

    wizard_id = fields.Many2one(
        comodel_name="documents.packing_list.wizard",
        required=True,
        ondelete="cascade",
    )
    number = fields.Integer(string="Number")
    partner_id = fields.Many2one("res.partner", string="Customer")
    package_count = fields.Integer(string="Package Count")
    volume = fields.Float(string="Volume") 